package com.blog.dao.impl;

import com.blog.dao.CategoryDao;
import com.blog.entity.Category;
import com.blog.util.DBUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class CategoryDaoImpl implements CategoryDao {

    @Override
    public int add(Category category) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        int rows = 0;

        try {
            conn = DBUtil.getConnection();
            String sql = "INSERT INTO category(name, description, user_id) VALUES (?, ?, ?)";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, category.getName());
            pstmt.setString(2, category.getDescription());
            pstmt.setInt(3, category.getUserId());

            rows = pstmt.executeUpdate();
        } catch (SQLException e) {
            System.err.println("添加分类失败：" + e.getMessage());
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt);
        }

        return rows;
    }

    @Override
    public int update(Category category) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        int rows = 0;

        try {
            conn = DBUtil.getConnection();
            String sql = "UPDATE category SET name=?, description=? WHERE id=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, category.getName());
            pstmt.setString(2, category.getDescription());
            pstmt.setInt(3, category.getId());

            rows = pstmt.executeUpdate();
        } catch (SQLException e) {
            System.err.println("更新分类失败：" + e.getMessage());
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt);
        }

        return rows;
    }

    @Override
    public int delete(int id) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        int rows = 0;

        try {
            conn = DBUtil.getConnection();
            // 先删除该分类下的所有文章
            String sql1 = "DELETE FROM article WHERE category_id=?";
            pstmt = conn.prepareStatement(sql1);
            pstmt.setInt(1, id);
            pstmt.executeUpdate();

            // 再删除分类
            String sql2 = "DELETE FROM category WHERE id=?";
            pstmt = conn.prepareStatement(sql2);
            pstmt.setInt(1, id);
            rows = pstmt.executeUpdate();
        } catch (SQLException e) {
            System.err.println("删除分类失败：" + e.getMessage());
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt);
        }

        return rows;
    }

    @Override
    public Category findById(int id) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Category category = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT * FROM category WHERE id=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);

            rs = pstmt.executeQuery();
            if (rs.next()) {
                category = new Category();
                category.setId(rs.getInt("id"));
                category.setName(rs.getString("name"));
                category.setDescription(rs.getString("description"));
                category.setUserId(rs.getInt("user_id"));
            }
        } catch (SQLException e) {
            System.err.println("查询分类失败：" + e.getMessage());
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }

        return category;
    }

    @Override
    public List<Category> findByUserId(int userId) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Category> categoryList = new ArrayList<>();

        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT * FROM category WHERE user_id=? ORDER BY id DESC";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, userId);

            rs = pstmt.executeQuery();
            while (rs.next()) {
                Category category = new Category();
                category.setId(rs.getInt("id"));
                category.setName(rs.getString("name"));
                category.setDescription(rs.getString("description"));
                category.setUserId(rs.getInt("user_id"));

                categoryList.add(category);
            }
        } catch (SQLException e) {
            System.err.println("查询用户分类失败：" + e.getMessage());
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }

        return categoryList;
    }

    @Override
    public List<Category> findAllWithArticleCount() {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Category> categoryList = new ArrayList<>();

        try {
            conn = DBUtil.getConnection();
            // 联表查询分类和对应的文章数量
            String sql = "SELECT c.*, COUNT(a.id) AS article_count " +
                    "FROM category c " +
                    "LEFT JOIN article a ON c.id = a.category_id " +
                    "GROUP BY c.id " +
                    "ORDER BY c.id DESC";

            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                Category category = new Category();
                category.setId(rs.getInt("id"));
                category.setName(rs.getString("name"));
                category.setDescription(rs.getString("description"));
                category.setUserId(rs.getInt("user_id"));

                // 扩展属性：文章数量
                categoryList.add(category);
            }
        } catch (SQLException e) {
            System.err.println("查询分类及文章数量失败：" + e.getMessage());
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }

        return categoryList;
    }
}